package beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.naming.NamingException;

import tools.DBManager;
import tools.Utility;

public class OrderBean {
	public int getID() {
		return ID;
	}

	public void setID(int iD) {
		ID = iD;
	}

	public int getUserID() {
		return userID;
	}

	public void setUserID(int userID) {
		this.userID = userID;
	}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public String getProductString() {
		return productString;
	}

	public void setProductString(String productString) {
		this.productString = productString;
	}

	public String getRecipient() {
		return recipient;
	}

	public void setRecipient(String recipient) {
		this.recipient = recipient;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public Timestamp getOrdertime() {
		return ordertime;
	}

	public void setOrdertime(Timestamp ordertime) {
		this.ordertime = ordertime;
	}

	public Timestamp getDeliveryTime() {
		return deliveryTime;
	}

	public void setDeliveryTime(Timestamp deliveryTime) {
		this.deliveryTime = deliveryTime;
	}

	private int ID;
	private int userID;
	private int status;
	private String productString;
	private String recipient;
	private String address;
	private String phone;
	private Timestamp ordertime;
	private Timestamp deliveryTime;

	private Connection conn = null;
	private Statement stmt = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;

	public OrderBean() {

	}

	public boolean newOrder(String recipient, String address, String phone,
			CartBean cart, UserBean user) {
		// new Timestamp(System.currentTimeMillis());

		try {
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format("SELECT * FROM t_order");
			rs = stmt.executeQuery(sqlCmd);

			rs.moveToInsertRow();

			rs.updateInt("UserID", user.getId());
			rs.updateInt("Status", 0);
			rs.updateString("Products", Utility
					.ConvertProductList_to_ProductString(cart));
			rs.updateString("Recipient", recipient);
			rs.updateString("Address", address);
			rs.updateString("Phone", phone);
			rs.updateTimestamp("OrderTime", new Timestamp(System
					.currentTimeMillis()));

			rs.insertRow();

		} catch (SQLException sqlException) {
			return false;
		} catch (NamingException namingException) {
			return false;
		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
		return true;
	}

	public boolean setOrderbyID(int orderID) {
		try {
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format(
					"SELECT * FROM t_order where t_order.ID = %d", orderID);
			rs = stmt.executeQuery(sqlCmd);

			rs.last();
			if (rs.getRow() != 1) {
				return false;
			} else {
				setID(rs.getInt("ID"));
				setUserID(rs.getInt("UserID"));
				setStatus(rs.getInt("Status"));
				setProductString(rs.getString("Products"));
				setRecipient(rs.getString("Recipient"));
				setAddress(rs.getString("Address"));
				setPhone(rs.getString("Phone"));
				setOrdertime(rs.getTimestamp("OrderTime"));
				setDeliveryTime(rs.getTimestamp("DeliveryTime"));
			}
		} catch (SQLException sqlException) {
			return false;
		} catch (NamingException namingException) {
			return false;
		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
		return true;

	}

	public boolean isValid(CartBean cart) {
		
		ArrayList<AssembleBean> result = new ArrayList<AssembleBean>();

		for (int i = 0; i < cart.getItems().size(); i++) {
			AssembleList assembleList = new AssembleList();
			if (i == 0) {
				assembleList.initial(cart.getItems().get(i).getProduct()
						.getID());
				result = times(assembleList.getComponents(), cart.getItems()
						.get(i).getNumber());
			} else {
				assembleList.initial(cart.getItems().get(i).getProduct()
						.getID());

				result = merge(result, times(assembleList.getComponents(), cart
						.getItems().get(i).getNumber()));
			}
		}
		
		//Query Database to check the result
		
		try {
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			for(int i = 0; i < result.size(); i++){
				String sqlCmd = String.format(
						"SELECT * FROM t_component where t_component.ID = %d", result.get(i).getComponentID());
				rs = stmt.executeQuery(sqlCmd);
				
				rs.last();
				if (rs.getRow() != 1) {
					return false;
				} else {
					if(rs.getInt("Quantity") < result.get(i).getComponentNO()){
						return false;
					}
				}
			}
				
		} catch (SQLException sqlException) {
			return false;
		} catch (NamingException namingException) {
			return false;
		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
		
		return true;
	}

	private ArrayList<AssembleBean> merge(ArrayList<AssembleBean> result,
			ArrayList<AssembleBean> addition) {
		for (int i = 0; i < addition.size(); i++) {
			AssembleBean assemble = addition.get(i);
			boolean addFlag = true;
			for (int n = 0; n < result.size(); n++) {
				if (result.get(n).getComponentID() == assemble.getComponentID()) {
					result.get(n).setComponentNO(
							result.get(n).getComponentNO()
									+ assemble.getComponentNO());
					addFlag = false;
				}
			}
			if (addFlag) {
				result.add(assemble);
			}
		}

		return result;
	}

	private ArrayList<AssembleBean> times(ArrayList<AssembleBean> original,
			int productQuantity) {
		for (int i = 0; i < original.size(); i++) {
			original.get(i).setComponentNO(
					original.get(i).getComponentNO() * productQuantity);
		}
		return original;
	}
	
	public boolean delete(CartBean cart){
		AssembleList assembleList = new AssembleList();
		ArrayList<AssembleBean> result = new ArrayList<AssembleBean>();

		for (int i = 0; i < cart.getItems().size(); i++) {
			if (i == 0) {
				assembleList.initial(cart.getItems().get(i).getProduct()
						.getID());
				result = times(assembleList.getComponents(), cart.getItems()
						.get(i).getNumber());
			} else {
				assembleList.initial(cart.getItems().get(i).getProduct()
						.getID());

				result = merge(result, times(assembleList.getComponents(), cart
						.getItems().get(i).getNumber()));
			}
		}
		
		try {
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);

			
			//delete components
			for(int i = 0; i < result.size(); i++){
				String sqlCmd = String.format(
						"SELECT * FROM t_component where t_component.ID = %d", result.get(i).getComponentID());
				rs = stmt.executeQuery(sqlCmd);
				
				rs.last();
				if (rs.getRow() != 1) {
					return false;
				} else {
					rs.updateInt("Quantity", rs.getInt("Quantity") - result.get(i).getComponentNO());
					rs.updateRow();
				}
			}	
		} catch (SQLException sqlException) {
			return false;
		} catch (NamingException namingException) {
			return false;
		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}		
		return true;
	}
	
	public boolean deliver(int orderID){
		try {
			conn = DBManager.getConnection();
			stmt = DBManager.getStatement(conn);
			String sqlCmd = String.format(
					"SELECT * FROM t_order where t_order.ID = %d", orderID);
			rs = stmt.executeQuery(sqlCmd);

			rs.last();
			if (rs.getRow() != 1) {
				return false;
			} else {
				rs.updateInt("Status", 1);
				rs.updateTimestamp("DeliveryTime", new Timestamp(System
					.currentTimeMillis()) );
				rs.updateRow();
			}
		} catch (SQLException sqlException) {
			return false;
		} catch (NamingException namingException) {
			return false;
		} finally {
			DBManager.close(rs);
			DBManager.close(stmt);
			DBManager.close(conn);
		}
		return true;
		
	}
}
